﻿CREATE function [dbo].[func_KiemTraViTriTrong]
(
	@DSHangGhe varchar(max),
	@SLHangGhe varchar(max),
	@SanBayDi varchar(10),
	@SanBayDen varchar(10),
	@ThoiGianBay datetime,
	@ThoiGianVe datetime
)
returns
@DSChuyenBay table
(
	MaChuyenBay int, 
	LoaiChuyenBay int
)
as
Begin
declare 
	@MaHangGhe int,
	@SoLuong int,
	@MaChuyenBay int,
	@LoaiChuyenBay int,
	@isNull int,
	@isThoa int	
declare
	chuyenBaycur cursor for select MaChuyenBay, LoaiChuyenBay from dbo.func_GetChuyenBay(@SanBayDi,@SanBayDen,@ThoiGianBay,@ThoiGianVe)
declare	
	hangGhecur cursor for select item, item1 from dbo.udf_List2Table(@DSHangGhe, @SLHangGhe,',')

	open chuyenBaycur
		fetch next from chuyenBaycur into  @MaChuyenBay, @LoaiChuyenBay
		while(@@FETCH_STATUS = 0)		
		begin
			set @isThoa = 1;
			open hangGhecur
				fetch next from hangGhecur into  @MaHangGhe, @SoLuong
				while(@@FETCH_STATUS = 0)
				begin
					set @isNull = (select count(*) from DanhSachHangGhe where MaChuyenBay = @MaChuyenBay and MaHangGhe = @MaHangGhe and SoViTriConTrong >=  @SoLuong)
					if (@isNull = 0)
						set @isThoa = 0;
					fetch next from hangGhecur into  @MaHangGhe, @SoLuong
				end
			close hangGhecur
			if (@isThoa <> 0)
			begin
				insert into @DSChuyenBay values (@MaChuyenBay, @LoaiChuyenBay)
			end
			fetch next from chuyenBaycur into   @MaChuyenBay, @LoaiChuyenBay
		end 
	close chuyenBaycur
	deallocate chuyenBaycur
	deallocate hangGhecur
	return
End

